﻿using Microsoft.Office.Interop.Excel;
using SalesQuery.Properties;
using Skynet.Data;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SalesQuery
{
    public partial class Form1 : Form
    {
        private DataSet dsPrint = new DataSet();

        public Form1()
        {
            InitializeComponent();

            Skynet.Data.PersistenceProperty.ConnectionString = Settings.Default.ConnString;
            //Skynet.Data.PersistenceProperty.ConnectionString = "Database=v7_0;user id=db2Admin;password=dancinget;server=192.168.22.235";   
            Skynet.Data.PersistenceProperty.DatabaseType = DatabaseType.DB2;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            cbAre.SelectedIndex = 0;
            cbOffice.DataSource = QueryOffice().Tables[0];
            cbOffice.DisplayMember = "OFFICE";
            cbOffice.ValueMember = "OFFICEID";
            cbOffice.SelectedIndex = -1;

        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private DataSet Query(DateTime dtStart, DateTime dtEnd, string leename)
        {
            IDataAccess data = DataAccessFactory.instance.CreateDataAccess();
            data.Open();
            try
            {
                string strFiler = string.Empty;

                string orderStr = string.Empty;
                if (leename.Trim() != string.Empty)
                {
                    string[] strarray = leename.Trim().Replace('，',',').Split(',');
                    if (strarray.Length > 1)
                    {
                        if (rbName.Checked == true)
                        {
                            strFiler = " AND (";
                            foreach (string str in strarray)
                            {
                                strFiler = strFiler + " T_CODEX_DETAIL.TRADENAME LIKE '" + str + "%' OR";
                            }

                            strFiler = strFiler.Substring(0, strFiler.Length - 2);
                            strFiler = strFiler + ") ";
                        }
                        else
                        {
                            strFiler = " AND (";
                            foreach (string str in strarray)
                            {
                                strFiler = strFiler + " T_CODEX_DETAIL.LEECHDOMDETAILNO = '" + str + "' OR";
                            }

                            strFiler = strFiler.Substring(0, strFiler.Length - 2);
                            strFiler = strFiler + ") ";
                        }

                        orderStr = " ORDER BY TRADENAME";
                    }
                    else
                    {
                        if (rbName.Checked == true)
                        {
                            strFiler = " AND T_CODEX_DETAIL.TRADENAME LIKE :LEENAME";
                        }
                        else
                        {
                            strFiler = " AND T_CODEX_DETAIL.LEECHDOMDETAILNO = :LEECHDOMID";
                        }
                    }
                }

                if (cbOffice.Text.Trim() != string.Empty)
                {
                    strFiler = strFiler + " AND EXECPHARMACYID = '" + cbOffice.SelectedValue + "'";
                }

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                if (cbAre.Text == "门诊")
                {
                    sql = " select T_OFFICE.OFFICE 开单科室,K.USERNAME as 开单医生,P.PATIENTNAME as 病人姓名,sum(L.AMOUNT) as 数量, L.OPERATEDATE as 收费时间,sum(L.TOTALMONEY) 金额" +
                        " ,TRADENAME as 药品名称,T_CODEX_DETAIL.LEECHDOMDETAILNO 药品编码  " +
                        " from P_DIAGSENDLEECHDOM L,T_PATIENT_INFO P,T_OPERATOR O,T_USERS K,T_CODEX_DETAIL,T_OFFICE " +
                        " where P.DIAGNOSEID=L.DIAGNOSEID AND L.OPERATORID=O.OPERATORID AND L.CLINICDOCTORID=K.USERID " +
                        " AND L.LEECHDOMNO = T_CODEX_DETAIL.LEECHDOMDETAILNO   " +
                        " AND ECIPEOPERATEDATE>=:DTSTART " +
                        " AND ECIPEOPERATEDATE<=:DTEND " +
                        " and L.REGISTEROFFICEID =  T_OFFICE.OFFICEID" +
                        strFiler +
                        " group by TRADENAME,LEECHDOMDETAILNO,T_OFFICE.OFFICE,P.PATIENTNAME, L.OPERATEDATE,K.USERNAME ";

                    if (orderStr != string.Empty)
                    {
                        sql = sql + orderStr;
                    }
                    else
                    {

                        sql = sql + " order by USERNAME";
                    }


                }
                else
                {
                    sql = " SELECT USERNAME AS 开单医生,OFFICE AS 开单科室,INHOSID AS 住院号,OFFICEBEDID AS 床号,PATIENTNAME AS 病人姓名, " +
                        " SUM(AMOUNT) AS 数量,OPERATEDATE AS 记账日期,SUM(MONEY) AS 金额,TRADENAME as 药品名称,LEECHDOMDETAILNO 药品编码 FROM (" +
                        " SELECT T_CODEX_DETAIL.TRADENAME,T_CODEX_DETAIL.LEECHDOMDETAILNO,K.USERNAME, L.INHOSID,T.OFFICE,P.PATIENTNAME,H.OFFICEBEDID,L.AMOUNT,L.UNITPRICE,L.OPERATEDATE,MONEY" +
                        " FROM T_USERS K,P_INHOSSENDLEECHDOM L,H_INHOS_RECORD H,T_PATIENT_INFO P,T_OFFICE T,T_CODEX_DETAIL " +
                        " WHERE L.INHOSID=H.INHOSID " +
                        " AND L.CURRENTINHOSMARK=H.CURRENTINHOSMARK" +
                        " AND P.DIAGNOSEID=H.DIAGNOSEID " +
                        " AND L.BILLOFOFFICEID=T.OFFICEID " +
                        " AND K.USERID=L.BILLOFDOCTORID" +
                        " AND L.OPERATEDATE>=:DTSTART " +
                        " AND L.OPERATEDATE<=:DTEND " +
                        " AND L.LEECHDOMNO = T_CODEX_DETAIL.LEECHDOMDETAILNO" +
                        strFiler +
                        " UNION ALL" +
                        " SELECT T_CODEX_DETAIL.TRADENAME,T_CODEX_DETAIL.LEECHDOMDETAILNO,K.USERNAME, L.INHOSID,T.OFFICE,P.PATIENTNAME,H.OFFICEBEDID,L.AMOUNT,L.UNITPRICE,L.OPERATEDATE ,MONEY" +
                        " FROM T_USERS K,P_LEAVEHOSSENDLEECHDOM L,H_LEAVEHOS_RECORD H,T_PATIENT_INFO P,T_OFFICE T ,T_CODEX_DETAIL" +
                        " WHERE L.INHOSID=H.INHOSID " +
                        " AND L.CURRENTINHOSMARK=H.CURRENTINHOSMARK" +
                        " AND P.DIAGNOSEID=H.DIAGNOSEID " +
                        " AND L.BILLOFOFFICEID=T.OFFICEID " +
                        " AND K.USERID=L.BILLOFDOCTORID " +
                        " AND L.OPERATEDATE>=:DTSTART " +
                        " AND L.OPERATEDATE<=:DTEND " +
                        " AND L.LEECHDOMNO = T_CODEX_DETAIL.LEECHDOMDETAILNO" +
                        strFiler +
                        " ) T" +
                        " GROUP BY TRADENAME,LEECHDOMDETAILNO,USERNAME , INHOSID ,OFFICE ,PATIENTNAME ,OFFICEBEDID ,OPERATEDATE ";

                    if (orderStr != string.Empty)
                    {
                        sql = sql + orderStr;
                    }
                    else
                    {

                        sql = sql + " order by OFFICE ,PATIENTNAME";
                    }
                }


                para = new QueryParameterCollection(4);
                para.Add(":DTSTART", dtStart);
                para.Add(":DTEND", dtEnd);
                para.Add(":LEENAME", leename + "%");
                para.Add(":LEECHDOMID", leename);
                data.ExecuteDataset(sql, para, ds);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                data.Close();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            dsPrint =Query(dtStart.Value, dtEnd.Value, txtName.Text);
            dataGridView1.DataSource = dsPrint.Tables[0];
        }

        public void Save()
        {
            string fileName = "";
     

            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "Excel文件(*.xls)|*.xls"; //设置对话框保存的文件类型  
            saveFileDialog1.Title = "保存导出数据......";                       //设置对话框标题  
            saveFileDialog1.InitialDirectory = @"C:\Users\Administrator\Documents";                          //设置初始保存路径  
            saveFileDialog1.RestoreDirectory = true;                            //设置保存对话框是否记忆上次打开的目录  
            saveFileDialog1.ShowHelp = true;  

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {

                fileName = saveFileDialog1.FileName;   //文件名
                SuperToExcel(dsPrint.Tables[0], fileName);
            }
        }
        //高效导出Excel
        public static bool SuperToExcel(System.Data.DataTable excelTable, string filePath)
        {
            Microsoft.Office.Interop.Excel.Application app =
                new Microsoft.Office.Interop.Excel.ApplicationClass();
            try
            {
                app.Visible = false;
                Workbook wBook = app.Workbooks.Add(true);
                Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
                Microsoft.Office.Interop.Excel.Range range;
                int colIndex = 0;
                int rowIndex = 0;
                int colCount = excelTable.Columns.Count;
                int rowCount = excelTable.Rows.Count;

                //创建缓存数据
                object[,] objData = new object[rowCount + 1, colCount];

                //写标题
                int size = excelTable.Columns.Count;
                for (int i = 0; i < size; i++)
                {
                    wSheet.Cells[1, 1 + i] = excelTable.Columns[i].Caption;
                }
                range = (Range)wSheet.get_Range(app.Cells[1, 1], app.Cells[1, colCount]);
                range.Interior.ColorIndex = 15;//背景色 灰色
                range.Font.Bold = true;//粗字体
                //获取实际数据

                for (rowIndex = 0; rowIndex < rowCount; rowIndex++)
                {
                    for (colIndex = 0; colIndex < colCount; colIndex++)
                    {
                        objData[rowIndex, colIndex] = excelTable.Rows[rowIndex][colIndex].ToString();
                    }
                }


                //写入Excel 
                range = (Range)wSheet.get_Range(app.Cells[2, 1], app.Cells[rowCount + 1, colCount]);
                range.NumberFormatLocal = "@";//设置数字文本格式
                range.Value2 = objData;
                //Application.DoEvents();

                wSheet.Columns.AutoFit();

                //设置禁止弹出保存和覆盖的询问提示框 
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;

                wBook.Saved = true;
                wBook.SaveCopyAs(filePath);

                app.Quit();
                app = null;
                GC.Collect();
                return true;
            }
            catch (Exception err)
            {
                MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Save();
        }

        private DataSet QueryOffice()
        {
            IDataAccess data = DataAccessFactory.instance.CreateDataAccess();
            data.Open();
            try
            {
                string sql = "";
                DataSet ds = new DataSet();
                sql = "select * from T_OFFICE where OFFICETYPE = '药房'";
                data.ExecuteDataset(sql, ds);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                data.Close();
            }
        }
    }
}
